Criando Stored Procedures e Functions no MySQL 8.4

Introdução

Stored Routines (Rotinas Armazenadas) no MySQL são conjuntos de uma ou mais instruções SQL pré-compiladas e armazenadas no banco de dados, que podem ser executadas sob demanda. Elas vêm em duas formas principais:

1. Stored Procedures (Procedimentos Armazenados): Executam um conjunto de ações, podem receber parâmetros de entrada (IN), retornar parâmetros de saída (OUT) ou modificar parâmetros de entrada/saída (INOUT). São invocados usando a instrução CALL.
2. Stored Functions (Funções Armazenadas): Executam um conjunto de ações e obrigatoriamente retornam um único valor de um tipo específico. Podem receber apenas parâmetros de entrada (IN). São invocadas como parte de uma expressão SQL (por exemplo, em um SELECT ou SET).

Vantagens de usar Stored Routines:

• Reutilização de Código: Escreva a lógica uma vez e chame-a várias vezes.
• Melhor Desempenho: O código é pré-compilado e armazenado no servidor, reduzindo o tráfego de rede e o overhead de análise.
• Segurança Aprimorada: Conceda permissões para executar a rotina sem dar acesso direto às tabelas subjacentes (usando SQL SECURITY DEFINER).
• Abstração da Lógica de Negócios: Encapsula regras de negócios complexas no banco de dados.
• Consistência: Garante que a mesma lógica seja aplicada sempre que a rotina for chamada.

Sintaxe Geral

A sintaxe básica para criar procedimentos e funções é:

Para Procedures:

CREATE
    [DEFINER = user]
    PROCEDURE [IF NOT EXISTS] nome_procedimento ([parametro_proc[,...]])
    [caracteristica ...]
    corpo_rotina

Para Functions:

CREATE
    [DEFINER = user]
    FUNCTION [IF NOT EXISTS] nome_funcao ([parametro_func[,...]])
    RETURNS tipo_retorno
    [caracteristica ...]
    corpo_rotina

Componentes da Sintaxe:

• parametro_proc:

  [ IN | OUT | INOUT ] nome_parametro tipo_dado

• parametro_func:

  nome_parametro tipo_dado

• tipo_dado: Qualquer tipo de dado válido do MySQL (ex: INT, VARCHAR(50), DATE, DECIMAL(10,2)).
• tipo_retorno: (Apenas para FUNCTION) O tipo de dado do valor que a função retornará.
• caracteristica: Opções que definem o comportamento e as propriedades da rotina:

  {
      COMMENT 'string_comentario'
    | LANGUAGE SQL
    | [NOT] DETERMINISTIC
    | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
    | SQL SECURITY { DEFINER | INVOKER }
  }

• corpo_rotina: Uma instrução SQL válida ou um bloco BEGIN ... END contendo múltiplas instruções SQL, declarações de variáveis, loops, condicionais, etc.

Explicação Detalhada dos Componentes

CREATE PROCEDURE / CREATE FUNCTION

• Instruções usadas para criar uma nova rotina armazenada.
• Por padrão, a rotina é associada ao banco de dados atual. Para associá-la explicitamente a outro banco, use nome_banco.nome_rotina.
• CREATE FUNCTION também é usado para criar Loadable Functions (Funções Carregáveis), que são funções externas escritas em C/C++ e carregadas no servidor. Funções armazenadas e carregáveis compartilham o mesmo namespace.

DEFINER = user

• Opcional. Especifica a conta MySQL ('user_name'@'host_name', CURRENT_USER, ou CURRENT_USER()) cujos privilégios serão verificados durante a execução da rotina, se a característica SQL SECURITY DEFINER estiver ativa (que é o padrão).
• Se omitido, o DEFINER padrão é o usuário que executa a instrução CREATE.
• A conta DEFINER precisa ter os privilégios necessários para executar as instruções dentro do corpo da rotina se SQL SECURITY for DEFINER.
• Os privilégios necessários para definir um DEFINER específico dependem dos privilégios do usuário que está criando a rotina (veja Seção 27.6 do manual).

IF NOT EXISTS

• Opcional. Evita que um erro ocorra se já existir uma rotina (procedure ou function) com o mesmo nome.
• Se uma rotina com o mesmo nome já existe:
  • A instrução CREATE ... IF NOT EXISTS é ignorada (com um aviso se for uma função com nome igual a uma função nativa ou carregável).
  • Sem IF NOT EXISTS, um erro ocorreria.

Lista de Parâmetros (...)

• Obrigatória, mesmo que vazia (()).
• Nomes de parâmetros não são sensíveis a maiúsculas/minúsculas.
• Para Procedures (parametro_proc):
  • IN: (Padrão) Passa um valor para dentro do procedimento. Modificações no valor dentro do procedimento não são visíveis para quem chamou após o retorno.
  • OUT: Passa um valor para fora do procedimento (retorno). O valor inicial dentro do procedimento é NULL. O valor final é visível para quem chamou. Requer uma variável de usuário (ex: @variavel) ou outra variável válida na chamada CALL.
  • INOUT: Combina IN e OUT. O valor é inicializado por quem chama, pode ser modificado pelo procedimento, e a modificação é visível para quem chamou após o retorno. Requer uma variável na chamada CALL.
• Para Functions (parametro_func):
  • Todos os parâmetros são implicitamente IN. Não se pode usar OUT ou INOUT.

RETURNS tipo_retorno

• Obrigatório apenas para FUNCTION.
• Define o tipo de dado do valor único que a função retornará.
• O corpo da função (corpo_rotina) deve conter uma instrução RETURN valor.
• Se o valor retornado por RETURN for de um tipo diferente do tipo_retorno, o MySQL tentará converter (coagir) o valor para o tipo correto.

caracteristica (Characteristics)

Define propriedades importantes da rotina:

• COMMENT 'string_comentario': (Extensão MySQL) Adiciona um comentário descritivo à rotina, visível com SHOW CREATE PROCEDURE/SHOW CREATE FUNCTION.
• LANGUAGE SQL: Indica que a rotina é escrita em SQL. Atualmente, o MySQL só suporta LANGUAGE SQL, então esta cláusula é informativa e ignorada.
• [NOT] DETERMINISTIC:
  • DETERMINISTIC: Indica que a rotina sempre produz o mesmo resultado para os mesmos parâmetros de entrada. Exemplos: Uma função que calcula a + b.
  • NOT DETERMINISTIC: (Padrão) Indica que a rotina pode produzir resultados diferentes para os mesmos parâmetros de entrada. Exemplos: Funções que usam NOW(), RAND(), ou que leem/modificam dados em tabelas (pois os dados podem mudar entre chamadas).
  • Importância:
    • O MySQL confia na declaração do criador (não verifica internamente).
    • Declarar incorretamente pode afetar a otimização de consultas e a segurança da replicação binária.
    • Funções NOT DETERMINISTIC podem exigir o privilégio SUPER se o log binário estiver ativo (veja Seção 27.7 do manual).
• { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }:
  • Estas características são informativas no MySQL; o servidor não as usa para restringir o que a rotina pode fazer.
  • CONTAINS SQL: (Padrão) A rotina não lê nem escreve dados (ex: SET @x = 1).
  • NO SQL: A rotina não contém nenhuma instrução SQL.
  • READS SQL DATA: A rotina contém instruções que leem dados (ex: SELECT), mas não escrevem.
  • MODIFIES SQL DATA: A rotina contém instruções que podem escrever dados (ex: INSERT, DELETE, UPDATE).
• SQL SECURITY { DEFINER | INVOKER }:
  • Define o contexto de segurança sob o qual a rotina será executada.
  • DEFINER: (Padrão) A rotina executa com os privilégios do usuário especificado na cláusula DEFINER. Quem chama precisa apenas do privilégio EXECUTE sobre a rotina.
  • INVOKER: A rotina executa com os privilégios do usuário que a invocou (CALL ou SELECT). Quem chama precisa do privilégio EXECUTE sobre a rotina e dos privilégios necessários para executar as instruções dentro do corpo da rotina.

corpo_rotina (Routine Body)

• Contém o código SQL da rotina.
• Pode ser uma única instrução SQL (ex: SELECT * FROM tabela; ou RETURN a + b;).
• Pode ser um bloco composto BEGIN ... END; para múltiplas instruções.
• Blocos BEGIN...END podem conter:
  • Declarações de variáveis locais (DECLARE).
  • Cursores (DECLARE CURSOR).
  • Manipuladores de condição (DECLARE HANDLER).
  • Estruturas de controle (loops WHILE, REPEAT, LOOP; condicionais IF, CASE).
• Restrições:
  • Stored Functions não podem conter instruções que realizem COMMIT ou ROLLBACK explícito ou implícito. Procedures podem.
  • Stored Functions não podem retornar conjuntos de resultados diretamente (ex: SELECT * FROM tabela;). Use SELECT ... INTO variavel;. Procedures podem retornar result sets.
  • Instruções USE nome_banco; não são permitidas dentro de rotinas. A rotina opera implicitamente no banco de dados ao qual está associada. Use nomes qualificados (nome_banco.nome_tabela) para acessar outros bancos.
  • Não se pode referenciar parâmetros ou variáveis locais de rotina dentro de Prepared Statements criados na mesma rotina.
  • Veja a Seção 27.8 do manual para mais restrições.

Contexto de Segurança (DEFINER vs INVOKER)

• SQL SECURITY DEFINER (Padrão):
  • Quem executa? Os privilégios da conta DEFINER.
  • Privilégios necessários para chamar: EXECUTE na rotina.
  • Privilégios verificados na execução: A conta DEFINER precisa ter os privilégios para as ações dentro da rotina.
  • Caso de uso: Permitir que usuários com poucos privilégios executem ações controladas e seguras definidas por um usuário mais privilegiado (DEFINER).
  • CURRENT_USER() dentro da rotina: Retorna a conta DEFINER.
• SQL SECURITY INVOKER:
  • Quem executa? Os privilégios do usuário que chamou a rotina.
  • Privilégios necessários para chamar: EXECUTE na rotina e os privilégios para as ações dentro da rotina.
  • Privilégios verificados na execução: Os privilégios do usuário que chamou são verificados para cada instrução dentro da rotina.
  • Caso de uso: Criar rotinas genéricas cujo acesso aos dados depende de quem as está chamando.
  • CURRENT_USER() dentro da rotina: Retorna a conta do usuário que chamou.

Nota sobre Roles: Por padrão, ao executar uma rotina DEFINER, apenas os default roles da conta DEFINER são ativados, a menos que activate_all_roles_on_login esteja habilitado. Se a execução depender de privilégios de roles não padrão, use SET ROLE dentro da rotina (com cautela).

Privilégios Necessários

• Para Criar: Requer o privilégio CREATE ROUTINE. Se a cláusula DEFINER for usada para especificar um usuário diferente, podem ser necessários privilégios adicionais (geralmente SUPER ou privilégios específicos dependendo da conta definida).
• Para Modificar: Requer o privilégio ALTER ROUTINE.
• Para Executar: Requer o privilégio EXECUTE.
• Outorgados Automaticamente: Por padrão (automatic_sp_privileges=ON), o criador da rotina recebe automaticamente os privilégios ALTER ROUTINE e EXECUTE sobre a rotina criada.
• Log Binário: Se o log binário estiver ativo, CREATE FUNCTION pode exigir o privilégio SUPER se a função não for declarada como DETERMINISTIC ou READS SQL DATA/NO SQL.

Considerações Importantes

• SQL Mode: O MySQL armazena o sql_mode ativo no momento da criação/alteração da rotina e a executa sempre com esse sql_mode, independentemente do sql_mode da sessão que a invoca. A mudança de modo ocorre após a avaliação dos argumentos passados.
• Conflitos de Nomes: Se você criar uma rotina com o mesmo nome de uma função nativa do MySQL, use um espaço entre o nome e os parênteses ao definir e ao chamar (nome_rotina (...)) para evitar erro de sintaxe. É melhor evitar esses nomes.
• Manipulação de Tipos de Dados: O MySQL verifica tipos de dados e estouro (overflow) em atribuições a parâmetros e variáveis locais. Colunas CHARACTER SET e COLLATE podem ser especificadas; caso contrário, usam-se os padrões do banco de dados no momento da criação da rotina.
• DELIMITER: Ao definir rotinas no cliente mysql que contêm ponto e vírgula (;) internamente (especialmente com blocos BEGIN...END), você precisa mudar o delimitador padrão do cliente para algo diferente (ex: // ou $$) antes do CREATE e restaurá-lo depois do END.

DELIMITER //
 
CREATE PROCEDURE nome_procedimento()
BEGIN
  -- Várias instruções SQL aqui;
  SELECT * FROM tabela;
  INSERT INTO outra_tabela (coluna) VALUES (1);
END //  -- Usa o novo delimitador
 
DELIMITER ; -- Restaura o delimitador padrão

Exemplos Práticos

(Usando a tabela accounts do tutorial como exemplo)

Tabela accounts:

CREATE TABLE accounts (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100),
    balance DECIMAL(10, 2)
);
 
INSERT INTO accounts (name, balance) VALUES
('Cliente A', 1300.00),
('Cliente B', 2500.50),
('Cliente C', 800.75);

1. Stored Procedure Sem Parâmetros

Busca todos os dados da tabela accounts.

DELIMITER //
CREATE PROCEDURE fetchData()
BEGIN
    SELECT * FROM accounts;
END //
DELIMITER ;
 
-- Chamada:
CALL fetchData();

2. Stored Procedure Com Parâmetro IN

Busca os dados de uma conta específica pelo ID.

DELIMITER //
CREATE PROCEDURE fetch_data_in(IN uid INT)
BEGIN
    SELECT * FROM accounts WHERE id = uid;
END //
DELIMITER ;
 
-- Chamada:
CALL fetch_data_in(1);
CALL fetch_data_in(3);

3. Stored Procedure Com Parâmetro OUT

Calcula e retorna o saldo total de todas as contas.

DELIMITER //
CREATE PROCEDURE fetchDataOut(OUT totBalance DECIMAL(12, 2))
BEGIN
    -- Note que SELECT INTO é necessário para atribuir a variável OUT
    SELECT SUM(balance) INTO totBalance FROM accounts;
END //
DELIMITER ;
 
-- Chamada:
CALL fetchDataOut(@balance); -- @balance é uma variável de sessão para receber o valor
SELECT @balance;             -- Mostra o valor retornado

(Exemplo alternativo do Manual) Contar cidades por país:

DELIMITER //
CREATE PROCEDURE citycount (IN country CHAR(3), OUT cities INT)
BEGIN
  SELECT COUNT(*) INTO cities FROM world.city -- Assume tabela world.city
  WHERE CountryCode = country;
END //
DELIMITER ;
 
-- Chamada:
CALL citycount('JPN', @cities_jpn);
SELECT @cities_jpn;

4. Stored Procedure Com Parâmetro INOUT

Atualiza o saldo de uma conta ao sacar um valor, retornando o novo saldo.

DELIMITER //
CREATE PROCEDURE update_data(IN uid INT, INOUT amount DECIMAL(10, 2))
BEGIN
    DECLARE uBalance DECIMAL(10, 2);
 
    -- Pega o saldo atual
    SELECT balance INTO uBalance FROM accounts WHERE id = uid;
 
    -- Verifica se há saldo suficiente
    IF amount > uBalance THEN
        -- Sinaliza um erro customizado se não houver saldo
        SIGNAL SQLSTATE '45000' -- SQLSTATE genérico para erro do usuário
           SET MESSAGE_TEXT = 'Saldo insuficiente';
    ELSE
        -- Atualiza o saldo
        UPDATE accounts
        SET balance = uBalance - amount
        WHERE id = uid;
 
        -- Seleciona o NOVO saldo e o coloca na variável INOUT 'amount' para retorno
        SELECT balance INTO amount FROM accounts WHERE id = uid;
    END IF;
 
END //
DELIMITER ;
 
-- Chamada:
SET @withdraw_amount = 100.00; -- Define o valor a sacar
CALL update_data(1, @withdraw_amount); -- Passa o ID e a variável
SELECT @withdraw_amount; -- Mostra o NOVO saldo retornado na variável

5. Stored Function Simples

Cria uma função que retorna uma saudação.

-- Não precisa de DELIMITER aqui, pois não há ';' interno
CREATE FUNCTION hello (s CHAR(20))
RETURNS CHAR(50)
DETERMINISTIC -- Importante: esta função sempre retorna o mesmo para a mesma entrada
RETURN CONCAT('Hello, ', s, '!');
 
-- Chamada (dentro de uma expressão):
SELECT hello('world');

Invocando Stored Routines

• Procedures: Use a instrução CALL.

  CALL nome_procedimento(parametro_in, @variavel_out, @variavel_inout);

• Functions: Referencie a função diretamente em uma expressão SQL onde um valor é esperado.

  SELECT nome_funcao(parametro_in) AS resultado;
  SET @valor = nome_funcao(parametro_in);
  SELECT * FROM tabela WHERE coluna = nome_funcao(parametro_in);

Este guia abrange os principais aspectos da criação de Stored Procedures e Functions no MySQL 8.4, combinando a precisão do manual oficial com a praticidade dos exemplos do tutorial. Lembre-se de consultar o manual do MySQL para obter detalhes mais aprofundados sobre casos específicos e restrições.